package com.hdoit.ares.logical.brokerage.dao.impl;

import java.util.List;

import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import com.hdoit.ares.logical.brokerage.dao.ICarBrokerageRecordDao;
import com.hdoit.ares.logical.brokerage.model.CarBrokerageRecord;
import com.hdoit.ares.logical.brokerage.model.CarShareBrokerage;
import com.hdoit.ares.logical.cust.model.Customer;
import com.hdoit.ares.logical.viewmodel.brokerage.CarBrokerageRecordSearch;
import com.hdoit.ares.logical.viewmodel.cust.CustomerSearch;
import com.tennetcn.common.dao.base.IFinderExpression;
import com.tennetcn.common.dao.base.impl.SuperDao;
import com.tennetcn.common.enums.OrderEnum;
import com.tennetcn.common.message.DaoBaseException;
import com.tennetcn.common.message.PagerModel;
import com.tennetcn.common.utils.FinderExpressionFactory;

/**
 * @author      yujinhui
 * @email       
 * @createtime  2017年12月14日 11:30:37
 * @comment
 */
@Component
public class CarBrokerageRecordDaoImpl extends SuperDao<CarBrokerageRecord> implements ICarBrokerageRecordDao{

	@Override
	public List<CarBrokerageRecord> queryList(CarBrokerageRecordSearch search, PagerModel pagerModel) {
		IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("carBrokerageRecord.*","customer.userIdentity as userIdentity","customer.username as customerName")
		                .from(CarBrokerageRecord.class,"carBrokerageRecord")
		                .leftJoin(Customer.class, "customer")
		                .on("customer.id","carBrokerageRecord.customerId")
		                .addOrder("carBrokerageRecord.recordDate", OrderEnum.desc)
		                .setMainTableAlias("carBrokerageRecord");
		
		appendFinder(search,finderExpression);

        if(pagerModel == null){
            return queryList(finderExpression, search); 
          }
        return queryList(finderExpression, search, pagerModel);
	}

	
	private void appendFinder(CarBrokerageRecordSearch search, IFinderExpression finder) {
		IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("bro.id")
			  			.from(CarBrokerageRecord.class,"bro")
			  			.leftJoin(Customer.class,"cus")
			  			.on("bro.userId","cus.id");
		
		if(!StringUtils.isEmpty(search.getConfirmState())){
			finder.andWhere("carBrokerageRecord.confirmState like #{confirmState}")
				  .setParam("confirmState", "%"+search.getConfirmState()+"%");
		}
	
		if(!StringUtils.isEmpty(search.getCustomerName())){			
			finder.andWhere("customer.username like #{username}")					
				  .setParam("username", "%"+search.getCustomerName()+"%");			
		}

		if(search.getUserIdentity() != null){			
			finder.andWhere("customer.userIdentity = #{userIdentity}")		
			  	  .setParam("userIdentity", search.getUserIdentity());
		}
		
		if(search.getStartDate()!=null){
			finder.andWhere("TIMESTAMPDIFF(SECOND,#{startDate},carBrokerageRecord.recordDate)>=0")
				  .setParam("startDate", search.getStartDate());
		}
		
		if(search.getEndDate()!=null){
			finder.andWhere("TIMESTAMPDIFF(SECOND,#{endDate},carBrokerageRecord.recordDate)<=0")
				  .setParam("endDate", search.getEndDate());
		}
		
		if(!StringUtils.isEmpty(search.getCarNo())){
			finder.andWhere("carBrokerageRecord.carNo like #{carNo}")
				  .setParam("carNo", "%"+search.getCarNo()+"%");
		}
		
		if(!StringUtils.isEmpty(search.getCityId())){
			finder.andWhere("customer.cityId = #{cityId}")
				  .setParam("cityId", search.getCityId());
		}
		
		if(!StringUtils.isEmpty(search.getRefereeId())){
			IFinderExpression finderMoreExpression=FinderExpressionFactory.createExpression();
			
			finderMoreExpression.select("carShareBrokerage.`carBrokerageId`")
								.from(CarShareBrokerage.class,"carShareBrokerage")
								.andWhere("carShareBrokerage.`customerId` = #{cutomerId}");
			
			finder.andWhere("carBrokerageRecord.id in (" + finderMoreExpression.toSql() + ")")
				  .setParam("cutomerId", search.getRefereeId());
		}
	}
	
	
	@Override
	public int queryCount(CarBrokerageRecordSearch search) {
        IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
       
        finderExpression.select("count(carBrokerageRecord.id) as count")
        				.from(CarBrokerageRecord.class,"carBrokerageRecord")
        				.leftJoin(Customer.class, "customer")
        				.on("customer.id","carBrokerageRecord.customerId")
        				.setMainTableAlias("carBrokerageRecord");
		
		appendFinder(search,finderExpression);
		
		return queryCount(finderExpression, search);
	}
	
	@Override
	public CarBrokerageRecord queryModel(String key) throws DaoBaseException {
        IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("carBrokerageRecord.*","customer.userIdentity as userIdentity","customer.username as customerName")
		                .from(CarBrokerageRecord.class,"carBrokerageRecord")
		                .leftJoin(Customer.class, "customer")
		                .on("customer.id","carBrokerageRecord.customerId")
		                .andWhere("carBrokerageRecord.id = #{key}")
		                .setParam("key", key)
		                .setMainTableAlias("carBrokerageRecord");
		                
		
		return queryModel(finderExpression);
	}


	@Override
	public CarBrokerageRecord queryByTaskId(String taskId) {
		IFinderExpression finderExpression=FinderExpressionFactory.createExpression();	
			finderExpression.select("*")
							.from(CarBrokerageRecord.class)
							.andEq("taskId", taskId);
		return queryModel(finderExpression);
	}


	@Override
	public List<CarBrokerageRecord> queryCarBrokerageRecordForStatistic(CustomerSearch search) {
		IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("carBrokerageRecord.*")
						.from(CarBrokerageRecord.class,"carBrokerageRecord")
						.leftJoin(Customer.class,"customer")
						.on("customer.id","carBrokerageRecord.customerId")
						.andWhere("DATE_FORMAT( carBrokerageRecord.`recordDate`, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )");
		
		appendForStatistic(finderExpression,search);
		
		return queryList(finderExpression);
	}


	private void appendForStatistic(IFinderExpression finderExpression, CustomerSearch search) {
		if(!StringUtils.isEmpty(search.getCityId())){
			finderExpression.andWhere("customer.cityId = #{cityId}")
							.setParam("cityId",search.getCityId());
		}
	}

}
